use TTTLines2014
GO

SET LANGUAGE ENGLISH
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

print N'Salvo i codpren delle prenotazioni da cancellare'
go
declare @from datetime
set @from='2013-12-31 23:59:59' 

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..prenotazioni')) DROP TABLE prenotazioni
create table prenotazioni(codpren char(14))

insert into prenotazioni select distinct codpren 
from seqbig 
where datapart<@from order by codpren

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END


---------------------------------------
--cancello le prenotazioni
---------------------------------------

PRINT N'Cancello i dati da passeg'
GO
delete from passeg where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da dettax'
GO
delete from dettax where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da dettax_iva'
GO
DELETE FROM DETTAX WHERE CODPREN IN (SELECT CODPREN FROM PRENOTAZIONI)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da biglietti'
GO
delete from biglietti where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da movimenti'
GO
delete from movimenti where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da detbig'
GO
delete from detbig where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da entbig'
GO
delete from entbig where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da seqbig'
GO
delete from seqbig where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da biglie'
GO
delete from biglie where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

print N'Cancello i dati da commissioni_dett'
GO
delete from commissioni_dett where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

print N'commissioni_dett_pax'
GO
delete from commissioni_dett_pax where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

----------------------------------------
--cancello l'archivio delle prenotazioni
----------------------------------------

PRINT N'Cancello i dati da passeg_arch'
GO
delete from passeg_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da dettax_arch'
GO
delete from dettax_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da dettax_iva_arch'
GO
DELETE FROM DETTAX_IVA_ARCH WHERE CODPREN IN (SELECT CODPREN FROM PRENOTAZIONI)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da movimenti_arch'
GO
delete from movimenti_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da detbig_arch'
GO
delete from detbig_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da entbig_arch'
GO
delete from entbig_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da seqbig_arch'
GO
delete from seqbig_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

PRINT N'Cancello i dati da biglie_arch'
GO
delete from biglie_arch where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

print N'Cancello i dati da archivia_biglietti'
GO
delete from archivia_biglietti where codpren in (select codpren from prenotazioni)
IF @@ERROR<>0 ROLLBACK TRANSACTION

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

-------------------------------------------
-- cancello le corse che non hanno partenze nel 2013 o non hanno prenotazioni attive per il 2013
-------------------------------------------
print N'Cancello le corse'
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..corse_to_del')) DROP TABLE corse_to_del

create table corse_to_del(codcorsa char(16))

insert into corse_to_del select codcorsa from corse where 
	datapart<'2013-12-31 23:59:59' and 
	codcorsa not in (select codcorsa from seqbig)
print N'situaz'
GO
delete from situaz where codcorsa in (select codcorsa from corse_to_del)
IF @@ERROR<>0 ROLLBACK TRANSACTION

print N'jboard_corse'
GO
delete from jboard_corse where codcorsa in (select codcorsa from corse_to_del)
IF @@ERROR<>0 ROLLBACK TRANSACTION

print N'corse'
GO
delete from corse where codcorsa in (select codcorsa from corse_to_del)
IF @@ERROR<>0 ROLLBACK TRANSACTION

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END


--------------------------------------------------------------------------------
print 'pulisco le tabelle di log'
GO
truncate table logclient
truncate table loggest
truncate table midtier_errorlog
truncate table midtier_log

print 'pulisco chiusure e limitazioni e jboard'
GO
delete from chiusure where id_linea not in (select distinct id_linea from corse)
IF @@ERROR<>0 ROLLBACK TRANSACTION

delete from limitazioni where id_linea not in (select distinct id_linea from corse)
IF @@ERROR<>0 ROLLBACK TRANSACTION

delete from limitazioni_formule where id_linea not in (select distinct id_linea from corse)
IF @@ERROR<>0 ROLLBACK TRANSACTION

delete from jboard_pax where jboard_id not in (select distinct jboard_id from jboard_corse)
IF @@ERROR<>0 ROLLBACK TRANSACTION

delete from jboard_vei where jboard_id not in (select distinct jboard_id from jboard_corse)
IF @@ERROR<>0 ROLLBACK TRANSACTION


----------------------------------------------------
print N'cancello le tabelle che ho usato come temporanee'
GO
----------------------------------------------------
drop table prenotazioni
drop table corse_to_del

commit transaction
